{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:03:47.415192Z",
     "start_time": "2019-04-21T02:03:46.969786Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:03:50.326236Z",
     "start_time": "2019-04-21T02:03:50.002845Z"
    }
   },
   "outputs": [],
   "source": [
    "raw_df = pd.read_csv('train_data.csv', sep=',')\n",
    "raw_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:03:55.074317Z",
     "start_time": "2019-04-21T02:03:55.065846Z"
    }
   },
   "outputs": [],
   "source": [
    "# 数据中有40列数值型，11列非数值型。\n",
    "# categories = []\n",
    "# numerics = []\n",
    "# cols = raw_df.columns\n",
    "# for i, col in enumerate(cols):\n",
    "#     if raw_df[cols[i]].dtype == object:\n",
    "#         categories.append(col)\n",
    "#     else:\n",
    "#         numerics.append(col)\n",
    "# print(categories)\n",
    "# print(len(categories))\n",
    "# print(numerics)\n",
    "# print(len(numerics))\n",
    "\n",
    "categories = ['rentType', 'houseType', 'houseFloor', 'houseToward', 'houseDecoration', 'communityName', 'city', \n",
    "              'region', 'plate', 'buildYear', 'tradeTime']\n",
    "\n",
    "numerics = ['ID', 'area', 'totalFloor', 'saleSecHouseNum', 'subwayStationNum', 'busStationNum', 'interSchoolNum', \n",
    "            'schoolNum', 'privateSchoolNum', 'hospitalNum', 'drugStoreNum', 'gymNum', 'bankNum', 'shopNum', 'parkNum', \n",
    "            'mallNum', 'superMarketNum', 'totalTradeMoney', 'totalTradeArea', 'tradeMeanPrice', 'tradeSecNum', \n",
    "            'totalNewTradeMoney', 'totalNewTradeArea', 'tradeNewMeanPrice', 'tradeNewNum', 'remainNewNum', 'supplyNewNum',\n",
    "            'supplyLandNum', 'supplyLandArea', 'tradeLandNum', 'tradeLandArea', 'landTotalPrice', 'landMeanPrice', 'totalWorkers',\n",
    "            'newWorkers', 'residentPopulation', 'pv', 'uv', 'lookNum', 'tradeMoney']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:03:56.776918Z",
     "start_time": "2019-04-21T02:03:56.604841Z"
    }
   },
   "outputs": [],
   "source": [
    "raw_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:04:04.358279Z",
     "start_time": "2019-04-21T02:04:04.332030Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "raw_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:04:14.223491Z",
     "start_time": "2019-04-21T02:04:14.198692Z"
    }
   },
   "outputs": [],
   "source": [
    "# 看一下类别型特征的包含类别\n",
    "for _, col in enumerate(categories):\n",
    "    print(col, raw_df[col].unique())\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:04:25.613926Z",
     "start_time": "2019-04-21T02:04:25.582182Z"
    }
   },
   "outputs": [],
   "source": [
    "# 类别型剔除：communityName，city，region，plate，tradeTime\n",
    "cates_feat =['rentType', 'houseType', 'houseFloor', 'houseToward', 'houseDecoration', 'buildYear']\n",
    "\n",
    "# 数值型剔除：'ID'，'supplyLandNum', 'tradeLandNum',\n",
    "num_feat = ['area', 'totalFloor', 'saleSecHouseNum', 'subwayStationNum', 'busStationNum', 'interSchoolNum', \n",
    "            'schoolNum', 'privateSchoolNum', 'hospitalNum', 'drugStoreNum', 'gymNum', 'bankNum', 'shopNum', 'parkNum', \n",
    "            'mallNum', 'superMarketNum', 'totalTradeMoney', 'totalTradeArea', 'tradeMeanPrice', 'tradeSecNum', \n",
    "            'totalNewTradeMoney', 'totalNewTradeArea', 'tradeNewMeanPrice', 'tradeNewNum', 'remainNewNum', 'supplyNewNum',\n",
    "             'supplyLandArea', 'tradeLandArea', 'landTotalPrice', 'landMeanPrice', 'totalWorkers',\n",
    "            'newWorkers', 'residentPopulation', 'pv', 'uv', 'lookNum', 'tradeMoney']\n",
    "label = ['tradeMoney']\n",
    "raw_df = raw_df[categories + num_feat]\n",
    "raw_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:04:35.861600Z",
     "start_time": "2019-04-21T02:04:35.740315Z"
    }
   },
   "outputs": [],
   "source": [
    "# 配套设施 合并： \n",
    "# stationNum ==>   'subwayStationNum', 'busStationNum'\n",
    "# schoolNum  ==>   'interSchoolNum','schoolNum', 'privateSchoolNum' ==》\n",
    "# medicalNum ==>   'hospitalNum', 'drugStoreNum'\n",
    "# lifeHouseNum ==> 'gymNum', 'bankNum', 'shopNum', 'parkNum', 'mallNum', 'superMarketNum'\n",
    "# 土地供需比 = 土地供应建筑面积/土地成交建筑面积，土地供需比越小，市场越供不应求。\n",
    "# landSupplyTradeRatio ==> supplyLandArea / tradeLandArea\n",
    "raw_df['stationNum'] = raw_df['subwayStationNum'] + raw_df['busStationNum']\n",
    "raw_df['schoolNum'] = raw_df['interSchoolNum'] + raw_df['schoolNum'] + raw_df['privateSchoolNum']\n",
    "raw_df['medicalNum'] = raw_df['hospitalNum'] + raw_df['drugStoreNum']\n",
    "raw_df['lifeHouseNum'] = raw_df['gymNum'] + raw_df['bankNum'] + raw_df['shopNum'] + raw_df['parkNum'] + raw_df['mallNum'] + raw_df['superMarketNum']\n",
    "raw_df['landSupplyTradeRatio'] = raw_df['supplyLandArea'] / raw_df['tradeLandArea']\n",
    "\n",
    "now_df = raw_df.drop(['subwayStationNum','busStationNum',\n",
    "                      'interSchoolNum', 'schoolNum', 'privateSchoolNum',\n",
    "                      'hospitalNum', 'drugStoreNum',\n",
    "                      'gymNum', 'bankNum', 'shopNum', 'parkNum', 'mallNum', 'superMarketNum',\n",
    "                      'supplyLandArea','tradeLandArea'], axis=1)\n",
    "now_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:27:33.682453Z",
     "start_time": "2019-04-21T02:27:32.526277Z"
    }
   },
   "outputs": [],
   "source": [
    "now_df.to_csv('now_df.csv', sep=',', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-21T02:22:57.548246Z",
     "start_time": "2019-04-21T02:22:57.530449Z"
    }
   },
   "outputs": [],
   "source": [
    "# 删除landSupplyTradeRatio列中为NaN的行\n",
    "# 先定位到所在行的index，然后对该index进行drop操作即可\n",
    "now_df.drop(now_df[np.isnan(now_df['landSupplyTradeRatio'])].index, inplace=True)\n",
    "now_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = pd.read_csv('./data_Future/train_data_filter01.csv')\n",
    "test_df = pd.read_csv('./data_Future/test_a.csv')\n",
    "train_df.head(5)\n",
    "test_ID = test_df['ID']\n",
    "\n",
    "\n",
    "y_train = train_df['tradeMoney']\n",
    "test_df.drop(\"ID\", axis = 1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import math\n",
    "def bins_ct (counts,i):\n",
    "    \n",
    "    n = counts   # n为数据的个数为55种楼层高度\n",
    "    K=1 + math.log(n) / math.log(2)  # 组数\n",
    "    # 极差  i为字段 如 totalFloor\n",
    "    poor = train.describe().loc['max'][i] - train.describe().loc['min'][i] \n",
    "# 组距=（最大值－最小值）÷组数\n",
    "    binsct = math.ceil(poor / K)\n",
    "    return binsct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "704\n"
     ]
    },
    {
     "ename": "NameError",
     "evalue": "name 'train' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-6-a75ba4415238>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mcounts\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mlen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtrain_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'totalTradeMoney'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalue_counts\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcounts\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mbinsct\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mbins_ct\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mcounts\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'totalTradeMoney'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      4\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[0mtrain_nei\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mset\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mtrain_df\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;34m'totalTradeMoney'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0munique\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-3-f9e98582a12c>\u001b[0m in \u001b[0;36mbins_ct\u001b[1;34m(counts, i)\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[0mK\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;36m1\u001b[0m \u001b[1;33m+\u001b[0m \u001b[0mmath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlog\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m/\u001b[0m \u001b[0mmath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlog\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m2\u001b[0m\u001b[1;33m)\u001b[0m  \u001b[1;31m# 组数\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      6\u001b[0m     \u001b[1;31m# 极差  i为字段 如 totalFloor\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 7\u001b[1;33m     \u001b[0mpoor\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mtrain\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdescribe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'max'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m-\u001b[0m \u001b[0mtrain\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdescribe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'min'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mi\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      8\u001b[0m \u001b[1;31m# 组距=（最大值－最小值）÷组数\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      9\u001b[0m     \u001b[0mbinsct\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mceil\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mpoor\u001b[0m \u001b[1;33m/\u001b[0m \u001b[0mK\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'train' is not defined"
     ]
    }
   ],
   "source": [
    "counts = len(train_df.loc[:,'totalTradeMoney'].value_counts())\n",
    "print(counts) \n",
    "binsct = bins_ct(counts,'totalTradeMoney')\n",
    "\n",
    "train_nei=set(train_df.loc[:,'totalTradeMoney'].unique())\n",
    "test_nei=set(test_df.loc[:,'totalTradeMoney'].unique())\n",
    "train_unique=train_nei-(train_nei&test_nei)\n",
    "test_unique=test_nei-(train_nei&test_nei)\n",
    "print('训练集二手房成交总金额(元)：{}'.format(train_unique))\n",
    "print('测试集二手房成交总金额(元)：{}'.format(test_unique))\n",
    "\n",
    "plt.clf()\n",
    "fig,axs=plt.subplots(1,2,figsize=(14,4))\n",
    "axs[0].hist(train_df.dropna().loc[:,'totalTradeMoney'],bins=binsct, edgecolor='black',alpha=0.5)\n",
    "axs[1].hist(test_df.dropna().loc[:,'totalTradeMoney'],bins=binsct, edgecolor='black',alpha=0.5)\n",
    "plt.show()\n",
    "fig.savefig('./img/17   二手房成交总金额.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
